A server (e.g., database) in a network can receive queries from one or more client devices (e.g., computers) that are connected to the network. A query may be, for example, a request to search or retrieve some data that is stored in the server. A process in the server will process and service the queries that are received by the server. When concurrent queries are sent to the server, the queries compete for system resources in the server, such as, for example, CPU (central processing unit) resources, data access, and data transport.
System schedulers in the server are used in conjunction with a priority of each query to maximize the utilization of the system resources and to reduce the query response time. System schedulers can under utilize the system resources, but have the advantage of ensuring a predictable query response time. Resource governors may also be used to automatically end a query that exceeds system resources that are allocated for the query. However, such resource threshold controls are generally difficult to maintain, and can incorrectly end a business-critical query.
The SQL query will typically access and process one or more rows which reside in one or more tables. Complex queries that join rows together across multiple tables can process large numbers of rows. Many queries are bounded in the number of rows they can process, and cannot consume system resources beyond the bounded limit. For example, a query which aggregates a column value over a single table will make a single pass over the rows of the target table.
A query which requires multiple passes over the same data set has the potential to consume system resources for an extended period of time. This is often a reasonable solution for the query, especially when the data set subject to multiple passes is compact or the number of passes over the data is limited. In some cases, the query plan which performs multiple passes over the same data set can consume system resources for an extended period of time. Such a query is typically the result of poor plan quality, and remedial action is required. This includes ending the offending query, and updating the table statistics to ensure poor cardinality is eliminated prior to query execution.
A problem that can occur in previous approaches is that a resource intense query has continued to consume system resources without any limits. These conditions can lead to a starvation situation, where other queries at the same or lower data-access priority are either starved or significantly slowed. Therefore, the current technology suffers from at least the above constraints and deficiencies.